using System.Globalization;
using System.Text.RegularExpressions;
using Crux.Core.LangChain.Abstractions.Databases;
using Crux.Core.LangChain.Abstractions.Databases.Metadatas;
using Microsoft.Data.Sqlite;
using Newtonsoft.Json;

namespace Crux.Core.LangChain.Databases.Sqlite;
/// <summary>
/// 
/// </summary>
/// <param name="connection"></param>
/// <param name="tableName"></param>
public class SqliteRagCollection(SqliteConnection connection, string tableName) : IVectorCollection
{
    private readonly SqliteConnection _connection = connection
        ?? throw new ArgumentNullException(nameof(connection));
    private readonly string tableName = tableName
        ?? throw new ArgumentNullException(nameof(tableName));

    private static string SerializeDocument(Vector document)
    {
        return JsonConvert.SerializeObject(document, SqliteRagConstants.SerializerSettings);
    }

    private static string SerializeVector(float[] vector)
    {
        return JsonConvert.SerializeObject(vector, SqliteRagConstants.SerializerSettings);
    }

    private async Task InsertDocument(string id, float[] vector, Vector document)
    {
        using var insertCommand = _connection.CreateCommand();

        string query = $"INSERT INTO {tableName} (id, vector, document) VALUES (@id, @vector, @document)";
        insertCommand.CommandText = query;
        insertCommand.Parameters.AddWithValue("@id", id);
        insertCommand.Parameters.AddWithValue("@vector", SerializeVector(vector));
        insertCommand.Parameters.AddWithValue("@document", SerializeDocument(document));
        await insertCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
    }

    /// <inheritdoc />
    public async Task<IReadOnlyCollection<string>> AddAsync(
        IReadOnlyCollection<Vector> items,
        CancellationToken cancellationToken = default)
    {
        items = items ?? throw new ArgumentNullException(nameof(items));

        foreach (var item in items)
        {
            if (item.Embedding is null)
            {
                throw new ArgumentException("Embedding is required", nameof(items));
            }

            await InsertDocument(item.Id, item.Embedding, new Vector
            {
                Text = item.Text,
                Metadata = item.Metadata,
            }).ConfigureAwait(false);
        }

        return items.Select(i => i.Id).ToArray();
    }

    private async Task<List<(Vector, float)>> SearchByVector(float[] vector, int k, CancellationToken cancellationToken = default)
    {
        using var searchCommand = _connection.CreateCommand();

        string query = $"SELECT id, vector, document, distance(vector, @vector) d FROM {tableName} ORDER BY d LIMIT @k";
        searchCommand.CommandText = query;
        searchCommand.Parameters.AddWithValue("@vector", SerializeVector(vector));
        searchCommand.Parameters.AddWithValue("@k", k);
        var res = new List<(Vector, float)>();

        using var reader = await searchCommand.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);

        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
        {
            var id = reader.GetString(0);
            var vec = await reader.GetFieldValueAsync<string>(1).ConfigureAwait(false);
            var doc = await reader.GetFieldValueAsync<string>(2).ConfigureAwait(false);

            var docDeserialized = JsonConvert.DeserializeObject<Vector>(doc, SqliteRagConstants.SerializerSettings) ?? new Vector
            {
                Text = string.Empty,
            };

            var distance = reader.GetFloat(3);
            res.Add((docDeserialized, distance));
        }

        return res;
    }

    private async Task DeleteDocument(string id)
    {
        using var deleteCommand = _connection.CreateCommand();

        string query = $"DELETE FROM {tableName} WHERE id=@id";
        deleteCommand.CommandText = query;
        deleteCommand.Parameters.AddWithValue("@id", id);
        await deleteCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
    }

    /// <inheritdoc />
    public async Task<VectorSearchResponse> SearchAsync(
        VectorSearchRequest request,
        VectorSearchSettings? settings = default,
        CancellationToken cancellationToken = default)
    {
        request = request ?? throw new ArgumentNullException(nameof(request));
        settings ??= new VectorSearchSettings();

        var documents = await SearchByVector(
            request.Embeddings.First(),
            settings.NumberOfResults,
            cancellationToken).ConfigureAwait(false);

        return new VectorSearchResponse
        {
            Items = documents.Select(d => new Vector
            {
                Text = d.Item1.Text,
                Metadata = d.Item1.Metadata,
                Distance = d.Item2,
            }).ToArray(),
        };
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="id"></param>
    /// <param name="cancellationToken"></param>
    /// <returns></returns>
    public async Task<Vector?> GetAsync(string id, CancellationToken cancellationToken = default)
    {
        using var command = _connection.CreateCommand();

        var query = $"SELECT vector, document FROM {tableName} WHERE id=@id";
        command.CommandText = query;
        command.Parameters.AddWithValue("@id", id);

        using var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);

        if (!await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
        {
            return null;
        }

        var vec = await reader.GetFieldValueAsync<string>(0, cancellationToken).ConfigureAwait(false);
        var doc = await reader.GetFieldValueAsync<string>(1, cancellationToken).ConfigureAwait(false);

        var docDeserialized = JsonConvert.DeserializeObject<Vector>(doc, SqliteRagConstants.SerializerSettings) ?? new Vector
        {
            Text = string.Empty,
        };

        return new Vector
        {
            Id = id,
            Text = docDeserialized.Text,
            Metadata = docDeserialized.Metadata,
            Embedding = JsonConvert.DeserializeObject<float[]>(vec, SqliteRagConstants.SerializerSettings)
        };
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="ids"></param>
    /// <param name="cancellationToken"></param>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    public async Task<bool> DeleteAsync(IEnumerable<string> ids, CancellationToken cancellationToken = default)
    {
        ids = ids ?? throw new ArgumentNullException(nameof(ids));

        foreach (var id in ids)
            await DeleteDocument(id).ConfigureAwait(false);

        return true;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="filters"></param>
    /// <param name="cancellationToken"></param>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    /// <exception cref="ArgumentException"></exception>
    public async Task<IReadOnlyList<Vector>> SearchByMetadata(Dictionary<string, object> filters, CancellationToken cancellationToken = default)
    {
        filters = filters ?? throw new ArgumentNullException(nameof(filters));

        using var command = _connection.CreateCommand();

        var query = $"SELECT id, vector, document FROM {tableName}";

        var whereClauses = new List<string>();
        int paramIndex = 0;

        foreach (var filter in filters)
        {
            if (string.IsNullOrWhiteSpace(filter.Key) || !IsValidJsonKey(filter.Key))
                throw new ArgumentException($"Invalid filter key: {filter.Key}", nameof(filters));

            var paramName = "@param" + paramIndex++;
            whereClauses.Add($"json_extract(document, '$.Metadata.{filter.Key}') = {paramName}");
            command.Parameters.AddWithValue(paramName, filter.Value);
        }
        query += " WHERE " + string.Join(" AND ", whereClauses);

        command.CommandText = query;
        var res = new List<Vector>();

        using var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);

        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
        {
            var id = await reader.GetFieldValueAsync<string>(0, cancellationToken).ConfigureAwait(false);
            var vec = await reader.GetFieldValueAsync<string>(1, cancellationToken).ConfigureAwait(false);
            var doc = await reader.GetFieldValueAsync<string>(2, cancellationToken).ConfigureAwait(false);

            var docDeserialized = JsonConvert.DeserializeObject<Vector>(doc, SqliteRagConstants.SerializerSettings) ?? new Vector
            {
                Text = string.Empty,
            };

            var vector = new Vector
            {
                Id = id,
                Text = docDeserialized.Text,
                Metadata = docDeserialized.Metadata,
                Embedding = JsonConvert.DeserializeObject<float[]>(vec, SqliteRagConstants.SerializerSettings),
            };

            res.Add(vector);
        }

        return res;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    static bool IsValidJsonKey(string input)
    {
        return Regex.IsMatch(input, @"^\w+$");
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="cancellationToken"></param>
    /// <returns></returns>
    /// <exception cref="NotImplementedException"></exception>
    public async Task<bool> IsEmptyAsync(CancellationToken cancellationToken = default)
    {
        using var command = _connection.CreateCommand();

        var query = $"SELECT COUNT(*) FROM {tableName}";
        command.CommandText = query;
        var count = await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);

        return count == null || Convert.ToInt32(count, CultureInfo.InvariantCulture) == 0;
    }
}
